<?php
	function f100($sql){
		//data handle instance
		//set request
		//define format
		//set format
		//define query string
		//process time
		//process fitler
		$dataHandle = new DataHandle();
		$dataHandle->setRequest("100");

		$action = getValue($_GET['action']);

		if ($action == "getItems"){
			$format = array(
				"Date" => "datem/d/Y",
			);

			$dataHandle->setFormat($format);
			$error = $dataHandle->processTime($startTime, $endTime);
			if($error == ""){
				$filter = $dataHandle->processFilters($startTime, $endTime, "mix_Produced_Header", "date");
				$query = getJSONQuery($startTime, $endTime, $filter);
				print($dataHandle->printJSON($sql, $query));
			}
			else
				print($error);
		}
		else if($action == "getYears"){
			$query = getYears();
			print($dataHandle->printJSON($sql, $query));
		}
		else{
			$temp = array();
			$temp['error'] = "100: Bad request";
			print(json_encode($temp));
		}

		unset($dataHandle);

	}

	function getJSONQuery(&$startTime, &$endTime, &$filter){

		$queryString = "
			WITH Picking AS (
				SELECT
				id_shift,
				COUNT(id_shift) AS tot
				FROM mix_Produced_Picking
				GROUP BY id_shift
			),
			Mixing AS (
				SELECT
				id_shift,
				COUNT(id_shift) AS tot
				FROM mix_Produced_Mixer
				GROUP BY id_shift
			)
			SELECT
				mix_Produced_Header.date								AS 'Date',
				Picking.tot 											AS 'Picking',
				Mixing.tot 												AS 'Mixing',
				mix_Produced_Header.number_working						AS 'Crew',
				mix_Produced_Header.working_minutes_per_day				AS 'Minutes'
				
			FROM
			mix_Produced_Header LEFT OUTER JOIN Mixing 
			ON mix_Produced_Header.id_shift = Mixing.id_shift 
			LEFT OUTER JOIN Picking 
			ON mix_Produced_Header.id_shift = Picking.id_shift";

		$queryString = $queryString.$filter;

		$oderByFiled = getValue($_GET['sortItem']);
		$oderByValue = getValue($_GET['ascDesc']);

		if($oderByFiled !== "" and $oderByValue !== "")
			$queryString = $queryString." order by ".$oderByFiled." ".$oderByValue;

		return $queryString;
	}

	function getYears(){
		$queryString = 
			"SELECT DISTINCT 
				DATEPART(year, date) AS availableYear
			FROM mix_Produced_Summary_View 
			ORDER BY availableYear desc";
		return $queryString;
	}
?>